import copy
import datetime
import time
from typing import List, Union

from sqlalchemy import Integer, func, or_
from sqlalchemy.orm import Session

from module_admin.entity.do.cms_car import CmsCar
from module_admin.entity.do.cms_car_team import CmsCarTeam
from module_admin.entity.do.cms_device import CmsDevice
from module_admin.entity.do.cms_driver import CmsDriver
from module_admin.entity.do.cms_order import CmsOrder
from module_admin.entity.do.cms_order_bill import CmsOrderBill
from module_admin.entity.do.cms_order_dispatch import CmsOrderDispatch
from module_admin.entity.do.cms_order_execute import CmsOrderExecute
from module_admin.entity.vo.cms_car_vo import CarModel, CarForQuery, CarModelForPage, CarModelAndFree, ArchivesMessage, \
    CarModelAndDetails, RealTimeMessage, StatsMessage, CarPageObject, CarModelForRecordList
from module_admin.entity.vo.cms_home_vo import MyTime
from module_admin.enum.state_enum import OrderStateEnum
from utils.bean_utils import copy_properties
from utils.time_format_util import object_format_datetime, list_format_datetime


class CmsCarDao:
    """
    部门管理模块数据库操作层
    """

    @classmethod
    def create_car(cls, db: Session, page_object: CarModel):
        object_dict = page_object.dict()
        if "car_image_list" in object_dict:
            del object_dict["car_image_list"]
        if "car_permit_image_list" in object_dict:
            del object_dict["car_permit_image_list"]
        cms_car = CmsCar(**object_dict)
        db.add(cms_car)
        db.flush()
        return cms_car

    @classmethod
    def delete_car_by_id(cls, db: Session, id: int):
        db.query(CmsCar) \
            .filter(CmsCar.id == id) \
            .delete()

    @classmethod
    def update_car_by_id(cls, db: Session, car_update_dict: dict):
        db.query(CmsCar) \
            .filter(CmsCar.id == car_update_dict.get('id')) \
            .update(car_update_dict)

    @classmethod
    def get_car_by_id(cls, db: Session, id: int):
        first = db.query(CmsCar).filter(CmsCar.id == id).first()
        return object_format_datetime(first)

    @classmethod
    def get_car_details_by_id(cls, db: Session, id: int):
        result = db.query(CmsCar.id.label("id"),
                          CmsCar.car_id.label("car_id"),
                          CmsCar.seat_num.label("seat_num"),
                          CmsCar.fixed_asset_code.label("fixed_asset_code"),
                          CmsCar.car_number_color.label("car_number_color"),
                          CmsCar.device_mode.label("device_mode"),
                          CmsCar.driver_id.label("driver_id"),
                          CmsCar.car_number.label("car_number"),
                          CmsCar.car_department_id.label("car_department_id"),
                          CmsCar.car_type.label("car_type"),
                          CmsCar.car_model.label("car_model"),
                          CmsCar.car_brand.label("car_brand"),
                          CmsCarTeam.car_team_name.label("car_team_name"),
                          CmsCarTeam.car_team_id.label("car_team_id"),
                          CmsCar.car_head.label("car_head"),
                          CmsCar.property_right.label("property_right"),
                          CmsCar.state.label("state"),
                          CmsCar.charging_type.label("charging_type"),
                          CmsCar.energy_type.label("energy_type"),
                          CmsCar.buy_date.label("buy_date"),
                          CmsCar.vin_no.label("vin_no"),
                          CmsCar.engine_o.label("engine_o"),
                          CmsCar.car_image.label("car_image"),
                          CmsCar.car_permit_image.label("car_permit_image"),
                          CmsDevice.car_device_no.label("car_device_no"),
                          CmsDevice.device_id.label("device_id"),
                          CmsDevice.device_type.label("device_type"),
                          CmsCar.remark.label("remark")
                          ) \
            .outerjoin(CmsCarTeam, CmsCar.car_team_id == CmsCarTeam.car_team_id) \
            .outerjoin(CmsDevice, CmsCar.device_id == CmsDevice.device_id) \
            .filter(CmsCar.id == id) \
            .one_or_none()
        archives_message = ArchivesMessage()
        archives_message.id = result.id
        archives_message.car_id = result.car_id
        archives_message.seat_num = result.seat_num
        archives_message.fixed_asset_code = result.fixed_asset_code
        archives_message.car_number_color = result.car_number_color
        archives_message.device_mode = result.device_mode
        archives_message.driver_id = result.driver_id
        archives_message.car_number = result.car_number
        archives_message.car_department_id = result.car_department_id
        archives_message.car_type = result.car_type
        archives_message.car_model = result.car_model
        archives_message.car_brand = result.car_brand
        archives_message.car_team_name = result.car_team_name
        archives_message.car_team_id = result.car_team_id
        archives_message.car_head = result.car_head
        archives_message.property_right = result.property_right
        archives_message.state = result.state
        archives_message.charging_type = result.charging_type
        archives_message.energy_type = result.energy_type
        archives_message.buy_date = result.buy_date
        archives_message.vin_no = result.vin_no
        archives_message.engine_o = result.engine_o
        archives_message.car_image = result.car_image
        archives_message.car_permit_image = result.car_permit_image
        archives_message.car_device_no = result.car_device_no
        archives_message.device_id = result.device_id
        archives_message.device_type = result.device_type
        archives_message.remark = result.remark
        return list_format_datetime(archives_message)

    @classmethod
    def is_busy(cls, db, id):
        now = datetime.datetime.now()
        cms_car_id = db.query(CmsCar.id) \
            .outerjoin(CmsOrderDispatch, CmsOrderDispatch.car_id == CmsCar.car_id) \
            .outerjoin(CmsOrder, CmsOrderDispatch.order_id == CmsOrder.order_id) \
            .filter(CmsOrder.plan_begin_time < now) \
            .filter(CmsOrder.plan_end_time > now) \
            .filter(CmsCar.id == id) \
            .group_by(CmsCar.id) \
            .one_or_none()
        return True if cms_car_id is not None else False

    @classmethod
    def get_car_order_by_id(cls, db, id):
        result = db.query(CmsCar.car_head.label("responsible_person"),
                          CmsDriver.driver_name.label("driver_name"),
                          CmsOrder.order_id.label("order_id")) \
            .outerjoin(CmsOrderDispatch, CmsOrderDispatch.car_id == CmsCar.car_id) \
            .outerjoin(CmsOrder, CmsOrderDispatch.order_id == CmsOrder.order_id) \
            .outerjoin(CmsDriver, CmsOrderDispatch.driver_id == CmsDriver.driver_id) \
            .filter(CmsOrder.state == OrderStateEnum.jxz.value) \
            .filter(CmsCar.id == id) \
            .one_or_none()
        real_time_message = RealTimeMessage()
        if result:
            real_time_message.responsible_person = result.responsible_person
            real_time_message.driver_name = result.driver_name
            real_time_message.order_id = result.execute_id
        return list_format_datetime(real_time_message)

    @classmethod
    def get_car_sum(cls, db, id):
        result = db.query(func.count(CmsOrderBill.id).label("sum_order_num"),
                          func.sum(CmsOrderBill.sheet_mileage).label("sum_mileage"),
                          func.count(CmsOrderBill.sheet_expenses).label("sum_expenses")) \
            .filter(CmsCar.id == id) \
            .one_or_none()
        stats_message = StatsMessage()
        stats_message.sum_mileage = result.sum_mileage
        stats_message.sum_order_num = result.sum_order_num
        stats_message.sum_expenses = result.sum_expenses
        return list_format_datetime(stats_message)

    @classmethod
    def get_car_by_car_id(cls, db: Session, car_id: str):
        first = db.query(CmsCar).filter(CmsCar.car_id == car_id).first()
        return object_format_datetime(first)

    @classmethod
    def list(cls, db: Session, query_object: CarForQuery):
        query_for_car = db.query(CmsCar.id.label("id"),
                                 CmsCar.car_id.label("car_id"),
                                 CmsCar.car_number.label("car_number"),
                                 CmsDriver.driver_name.label("driver_name"),
                                 CmsDevice.car_device_no.label("car_device_no")) \
            .outerjoin(CmsDevice, CmsCar.device_id == CmsDevice.device_id) \
            .outerjoin(CmsDriver, CmsDriver.driver_id == CmsCar.driver_id)
        filters = []
        if query_object.car_number and query_object.car_number.strip():
            filters.append(or_(CmsCar.car_number.like(f'%{query_object.car_number}%'),
                               CmsDevice.car_device_no.like(f'%{query_object.car_number}%')))
        if query_object.state:
            filters.append(CmsCar.state == query_object.state)
        if query_object.car_team_id and query_object.car_team_id.strip():
            filters.append(CmsCar.car_team_id == query_object.car_team_id)
        if query_object.car_department_id and query_object.car_department_id.strip():
            filters.append(CmsCar.car_department_id == query_object.car_department_id)
        if filters:
            query_for_car = query_for_car.filter(*filters)
        post_list_car: List[CarModelForPage] = list_format_datetime(
            query_for_car.order_by(CmsCar.create_time.desc()).all())
        post_list_car_page = []
        for e in post_list_car:
            car_model_for_page = CarModelForPage()
            car_model_for_page.id = e.id
            car_model_for_page.car_id = e.car_id
            car_model_for_page.car_number = e.car_number
            car_model_for_page.car_device_no = e.car_device_no
            car_model_for_page.driver_name = e.driver_name
            car_model_for_page.is_online = 1
            post_list_car_page.append(car_model_for_page)
        return post_list_car_page

    @classmethod
    def page(cls, db, query_object: CarPageObject):
        page_num = query_object.page_num
        page_size = query_object.page_size
        skip = (page_num - 1) * page_size  # 计算跳过的记录数
        limit = page_size  # 每页返回的记录数
        query_for_car = db.query(CmsCar.id.label("id"),
                                 CmsCar.car_id.label("car_id"),
                                 CmsCar.car_number.label("car_number"),
                                 CmsDriver.driver_name.label("driver_name"),
                                 CmsDevice.car_device_no.label("car_device_no")) \
            .outerjoin(CmsDevice, CmsCar.device_id == CmsDevice.device_id) \
            .outerjoin(CmsDriver, CmsDriver.driver_id == CmsCar.driver_id)
        filters = []
        if query_object.car_number and query_object.car_number.strip():
            filters.append(or_(CmsCar.car_number.like(f'%{query_object.car_number}%'),
                               CmsDevice.car_device_no.like(f'%{query_object.car_number}%')))
        if query_object.state:
            filters.append(CmsCar.state == query_object.state)
        if query_object.car_team_id and query_object.car_team_id.strip():
            filters.append(CmsCar.car_team_id == query_object.car_team_id)
        if query_object.car_department_id and query_object.car_department_id.strip():
            filters.append(CmsCar.car_department_id == query_object.car_department_id)
        if filters:
            query_for_car = query_for_car.filter(*filters)
        post_list_car: List[CarModelForPage] = list_format_datetime(
            query_for_car.order_by(CmsCar.create_time.desc()).offset(skip).limit(limit).all())
        post_list_car_page = []
        for e in post_list_car:
            car_model_for_page = CarModelForPage()
            car_model_for_page.id = e.id
            car_model_for_page.car_id = e.car_id
            car_model_for_page.car_number = e.car_number
            car_model_for_page.car_device_no = e.car_device_no
            car_model_for_page.driver_name = e.driver_name
            car_model_for_page.is_online = 1
            post_list_car_page.append(car_model_for_page)
        return post_list_car_page

    @classmethod
    def count(cls, db, query_object: CarPageObject):
        query_for_car = db.query(CmsCar.id.label("id")) \
            .outerjoin(CmsDevice, CmsCar.device_id == CmsDevice.device_id) \
            .outerjoin(CmsDriver, CmsDriver.driver_id == CmsCar.driver_id)
        filters = []
        if query_object.car_number and query_object.car_number.strip():
            filters.append(or_(CmsCar.car_number.like(f'%{query_object.car_number}%'),
                               CmsDevice.car_device_no.like(f'%{query_object.car_number}%')))
        if query_object.state:
            filters.append(CmsCar.state == query_object.state)
        if query_object.car_team_id and query_object.car_team_id.strip():
            filters.append(CmsCar.car_team_id == query_object.car_team_id)
        if query_object.car_department_id and query_object.car_department_id.strip():
            filters.append(CmsCar.car_department_id == query_object.car_department_id)
        if filters:
            query_for_car = query_for_car.filter(*filters)
        return query_for_car.order_by(CmsCar.create_time.desc()).count()

    @classmethod
    def list_all(cls, db: Session, new_plan_begin_time, new_plan_end_time):
        car__all = db.query(CmsCar).all()
        car_id_list = None
        if new_plan_begin_time is not None:
            sub_all = db.query(CmsCar.id) \
                .outerjoin(CmsOrderDispatch, CmsOrderDispatch.car_id == CmsCar.car_id) \
                .outerjoin(CmsOrder, CmsOrderDispatch.order_id == CmsOrder.order_id) \
                .filter(CmsOrder.plan_begin_time < new_plan_end_time) \
                .filter(CmsOrder.plan_end_time > new_plan_begin_time) \
                .group_by(CmsCar.id) \
                .all()
            car_id_list = {CmsCar.id for CmsCar in sub_all}
        list0 = []
        for e in car__all:
            free = CarModelAndFree()
            copy_properties(e, free)
            free.car_state = "任务中" if car_id_list is not None and free.id in car_id_list else "空闲"
            list0.append(free)
        return list_format_datetime(list0)

    @classmethod
    def list_free(cls, db: Session, new_plan_begin_time, new_plan_end_time):
        subquery = db.query(CmsCar.id) \
            .outerjoin(CmsOrderDispatch, CmsOrderDispatch.car_id == CmsCar.car_id) \
            .outerjoin(CmsOrder, CmsOrderDispatch.order_id == CmsOrder.order_id) \
            .filter(CmsOrder.plan_begin_time < new_plan_end_time) \
            .filter(CmsOrder.plan_end_time > new_plan_begin_time) \
            .group_by(CmsCar.id) \
            .subquery()
        # add
        result = db.query(CmsCar.id.label("id"),
                          CmsCar.car_id.label("car_id"),
                          CmsCar.car_number.label("car_number"),
                          CmsCar.car_department_id.label("car_department_id"),
                          CmsCar.car_team_id.label("car_team_id"),
                          CmsCar.device_id.label("device_id"),
                          CmsCar.state.label("state"),
                          CmsCar.create_time.label("create_time"),
                          CmsCar.car_image.label("car_image"),
                          CmsCar.car_permit_image.label("car_permit_image"),
                          CmsCar.car_type.label("car_type"),
                          CmsCar.car_brand.label("car_brand"),
                          CmsCar.car_model.label("car_model"),
                          CmsCar.car_head.label("car_head"),
                          CmsCar.property_right.label("property_right"),
                          CmsCar.energy_type.label("energy_type"),
                          CmsCar.buy_date.label("buy_date"),
                          CmsCar.vin_no.label("vin_no"),
                          CmsCar.engine_o.label("engine_o"),
                          CmsCar.charging_type.label("charging_type"),
                          CmsCar.remark.label("remark"),
                          CmsCar.create_by.label("create_by"),
                          CmsCar.update_time.label("update_time"),
                          CmsCar.update_by.label("update_by"),
                          CmsCar.seat_num.label("seat_num"),
                          CmsCar.fixed_asset_code.label("fixed_asset_code"),
                          CmsCar.car_number_color.label("car_number_color"),
                          CmsDriver.driver_name.label("driver_name"),
                          CmsDriver.phone.label("phone")) \
            .outerjoin(CmsDriver, CmsDriver.driver_id == CmsCar.driver_id) \
            .filter(CmsCar.id.notin_(subquery)).all()
        result2 = []
        for e in result:
            page = CarModelForRecordList()
            page.id = e.id
            page.car_id = e.car_id
            page.car_number = e.car_number
            page.car_department_id = e.car_department_id
            page.car_team_id = e.car_team_id
            page.device_id = e.device_id
            page.state = e.state
            page.create_time = e.create_time
            page.car_image = e.car_image
            page.car_permit_image = e.car_permit_image
            page.car_type = e.car_type
            page.car_brand = e.car_brand
            page.car_model = e.car_model
            page.car_head = e.car_head
            page.property_right = e.property_right
            page.energy_type = e.energy_type
            page.buy_date = e.buy_date
            page.vin_no = e.vin_no
            page.engine_o = e.engine_o
            page.charging_type = e.charging_type
            page.remark = e.remark
            page.create_by = e.create_by
            page.update_time = e.update_time
            page.update_by = e.update_by
            page.seat_num = e.seat_num
            page.fixed_asset_code = e.fixed_asset_code
            page.car_number_color = e.car_number_color
            page.driver_name = e.driver_name
            page.phone = e.phone
            result2.append(page)
        return list_format_datetime(result2)

    @classmethod
    def get_sum_free_by_time(cls, db: Session, time: MyTime):
        mileage = db.query(CmsCar.id.label("id")) \
            .outerjoin(CmsOrderDispatch, CmsOrderDispatch.car_id == CmsCar.car_id) \
            .outerjoin(CmsOrder, CmsOrderDispatch.order_id == CmsOrder.order_id) \
            .filter(or_(CmsOrder.plan_begin_time > time.end_time.strftime('%Y-%m-%d %H:%M:%S'),
                        CmsOrder.plan_end_time < time.start_time.strftime('%Y-%m-%d %H:%M:%S'),
                        CmsOrder.id.is_(None))) \
            .group_by(CmsCar.id) \
            .all()
        return len(mileage) if bool(mileage) else 0

    @classmethod
    def order_satrt_car_num(cls, db: Session, time: MyTime):
        mileage = db.query(func.count(CmsCar.id).label("id")) \
            .join(CmsOrderExecute, CmsOrderExecute.car_id == CmsCar.car_id) \
            .filter(CmsOrderExecute.create_time >= time.start_time.strftime('%Y-%m-%d %H:%M:%S')) \
            .filter(CmsOrderExecute.create_time < time.end_time.strftime('%Y-%m-%d %H:%M:%S')) \
            .scalar()
        return mileage if mileage is not None else 0
